package com.lawrence.demo;

import com.lawrence.util.DruidUtil;
import com.lawrence.util.JDBCUtil;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class JdbcDemo01 {
    //查询
//    public void selectAll() throws Exception {
//        String sql = "select * from Student";
//        Connection connection = JDBCUtil.getConnection();
//        PreparedStatement pre = connection.prepareStatement(sql);
//        ResultSet resultSet = pre.executeQuery();
//        ArrayList<Object> list = new ArrayList<Object>();
//        while (resultSet.next()) {
//            int id = resultSet.getInt("s_id");
//            String name = resultSet.getString("s_name");
//            list.add(id);
//            list.add(name);
//        }
//        connection.close();
//        System.out.println(list);
//    }

    //添加
    public void insert() throws Exception {
        String sql = "insert into Student(s_id,s_name) values(?,?)";
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement pre = connection.prepareStatement(sql);
        pre.setInt(1,2);
        pre.setString(2,"xiazhang");
        int i = pre.executeUpdate();
        connection.close();
        System.out.println(i);
    }

    //修改
    public void update() throws Exception {
        String sql = "update Student set s_name=? where s_id=?";
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement pre = connection.prepareStatement(sql);
        pre.setString(1,"hh - -修改过了");
        pre.setInt(2,5);
        int i = pre.executeUpdate();
        connection.close();
        System.out.println(i);
    }

    //删除
    public void delete() throws Exception {
        String sql = "delete from Student where s_id = ?";
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement pre = connection.prepareStatement(sql);
        pre.setInt(1,6);
        int i = pre.executeUpdate();
        connection.close();
        System.out.println(i);
    }

    //使用druid连接池
    //查询
    public void selectAll() throws Exception {
        String sql = "select * from Student";
        DataSource dataSource = DruidUtil.getDataSource();
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
        System.out.println(dataSource);
        PreparedStatement pre = connection.prepareStatement(sql);
        ResultSet resultSet = pre.executeQuery();
        ArrayList<Object> list = new ArrayList<Object>();
        while (resultSet.next()) {
            int id = resultSet.getInt("s_id");
            String name = resultSet.getString("s_name");
            list.add(id);
            list.add(name);
        }
        connection.close();
        System.out.println(list);
    }

}

